package nav;
import gao.tools.SQL;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.LineString;


public class DbManager {
	   
	Connection connection=null;  
	
	private String dbhost;
	private int dbport;
	private String dbuser;
	private String dbpasswd;
	private String dbname;
    
	private double left;
	private double right;
	private double top;
	private double bottom;
	
    private ResultSet resultSet;
    private Statement statement;
    
    private String filePath;
    
    
    public DbManager(double left, double right, double top, double bottom){
    	
    	dbhost="geo.informatik.fh-nuernberg.de";
    	dbport=5432;
        dbuser="dbuser";
        dbpasswd="dbuser";
        //dbname="deproDBMittelfranken";
        dbname = "deproDB2";
        
        filePath = "";
        
    	setBoundary(left,right,top,bottom);
   
    }
    
    public void init(){
    	
    	 try
	        {
	            /* Zugang zur Datenbank einrichten */
	            String connectionString="jdbc:postgresql://"+dbhost+":"+dbport+"/"+dbname;
	            connection=DriverManager.getConnection(connectionString,dbuser,dbpasswd);
	            connection.setAutoCommit(false);  //Getting results based on a cursor
	            System.out.println("Connected to database!");
	        }
	        catch (Exception e)
	        {
	            System.out.println("Error initialising DB access: "+e.toString());
	            e.printStackTrace();
	            System.exit(1);
	        }
    }
    
    public void run()throws Exception{
    	
    	long time=System.currentTimeMillis();

    	getStreets();
    	getCrossings();
    	getLinks();
        System.out.println("Connection closed after "+(System.currentTimeMillis()-time)/1000+" s");
        connection.close();
        
    }
    
    public void setFilePath(String path){

   		filePath=path; 	
    }
    
    private void setBoundary(double l,double r, double t,double d){
    	
    	if(l<=r||t<=d){
    		System.out.println("Makes no sense");
    		return;
    	}
    	this.left = l;
    	this.right = r;
    	this.top = t;
    	this.bottom = d;
    }
    
    private void getCrossings(){
    	System.out.print("Getting crossings.");

        try {
			statement=connection.createStatement();
			statement.setFetchSize(1000);
			resultSet = statement.executeQuery("SELECT id,partnr,gao_id,posnr,long,lat FROM crossing WHERE long>="+right +" AND long<="+left+" AND lat>="+bottom+" AND lat<="+top+" ORDER BY id,partnr");
			

			int cnt=0;
			BufferedWriter out = new BufferedWriter(new FileWriter(filePath+"crossings.txt"));
			
			while (resultSet.next())
			{
				if(cnt%1000==0)
					System.out.print(".");
			    int db_id=(int)resultSet.getLong(1);
			    int db_partnr=(int)resultSet.getLong(2);
			    String gao_id = resultSet.getString(3);
			    int posnr =(int)resultSet.getLong(4);
			    double db_long=resultSet.getDouble(5);
			    double db_lat=resultSet.getDouble(6);
			    out.write(db_id+" "+db_partnr+" "+ gao_id + " " + posnr + " " + db_long+" "+db_lat+"\n");
			    cnt++;
			}
			resultSet.close();
			out.close();
			System.out.println("Finished! Wrote "+cnt+ " crossings.");
		} catch (Exception e) {
			e.printStackTrace();
		}

    }
    
    private void getLinks(){
    	System.out.print("Getting links.");


        try {
			statement=connection.createStatement();
			statement.setFetchSize(1000);
			resultSet = statement.executeQuery("SELECT id,crossing_id_from,crossing_id_to,meters,long_from,lat_from,gao_id FROM link WHERE long_from>="+right +" AND long_from<="+left+" AND lat_from>="+bottom+" AND lat_from<="+top+" ORDER BY id");

			int cnt=0;
			BufferedWriter out = new BufferedWriter(new FileWriter(filePath+"links.txt"));
			
			while (resultSet.next())
			{
				if(cnt%1000==0)
					System.out.print(".");
			    int db_id=(int)resultSet.getLong(1);
			    int db_crossing_id_from=(int)resultSet.getLong(2);
			    int db_crossing_id_to=(int)resultSet.getLong(3);
			    int db_meters=(int)resultSet.getLong(4);
			    String gao_id=resultSet.getString(7);


			    out.write(+db_id+" "+db_crossing_id_from+" "+db_crossing_id_to+" "+db_meters+"m "+gao_id+"\n");
			    cnt++;
			}
			resultSet.close();
			out.close();
			System.out.println("Finished! Wrote "+cnt+ " links.");
		} catch (Exception e) {

			e.printStackTrace();
		}
    }
    
    
    private void getStreets(){
        try {
			System.out.print("Getting Streets.");


			statement=connection.createStatement();
			statement.setFetchSize(1000);
			resultSet = statement.executeQuery("SELECT realname, geodata_line,gao_id FROM domain WHERE geometry='L' AND lsiclass BETWEEN 35000 AND 35999 AND "+
			                                   SQL.createIndexQuery(left,top,right,bottom,SQL.OVERLAPS)
			                                  );

			int cnt=0;
			BufferedWriter out = new BufferedWriter(new FileWriter(filePath+"streets.txt"));
			while (resultSet.next())
			{
				if(cnt%1000==0)
					System.out.print(".");
			    String realname=resultSet.getString(1);
			    byte[] geodata_line=resultSet.getBytes(2);
			    String gao_id = resultSet.getString(3);
			    Geometry geom=SQL.wkb2Geometry(geodata_line);
			    out.write(gao_id+" "+realname+ "##");
			    LineString listring=((LineString)geom);
	            int n=listring.getNumPoints();

	            for (int i=0;i<n;i++)
	            {

	                 Coordinate coord=listring.getCoordinateN(i);
	                 out.write(coord.x+" "+coord.y+",");
	            }
			    cnt++;
			    out.write("\n");
			}
			resultSet.close();
			out.close();
			System.out.println("Finished! Wrote "+cnt+ " streets.");
		} catch (Exception e) {
			e.printStackTrace();
		}
        
    }
    

    

}
